﻿using Dapper;
using ThresholdAssistant.ConfigSdk;
using ThresholdAssistant.Dapper.Enums;
using ThresholdAssistant.Dapper.Extensions;
using ThresholdAssistant.DataContract.Page;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace ThresholdAssistant.Dapper
{
    /// <summary>
    /// SQL Server Dapper客户端
    /// </summary>
    public class MsSqlDapperClient
    {
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public readonly string ConnectionString = "";

        /// <summary>
        /// 默认构造函数
        /// </summary>
        public MsSqlDapperClient()
        {
            string connectionName = "ConnectionStrings:KFPTHosts";
            ConnectionString = ConfigUtil.GetValue(connectionName);
        }

        /// <summary>
        /// 初始化连接字符串
        /// </summary>
        /// <param name="connectionName">初始化连接字符串名称</param>
        public MsSqlDapperClient(string connectionName)
        {
            ConnectionString = ConfigUtil.GetValue(connectionName);
        }

        /// <summary>
        /// 初始化连接字符串
        /// </summary>
        /// <param name="connectionString">初始化连接字符串名称</param>
        /// <param name="isConnectName">是否是配置名称</param>
        public MsSqlDapperClient(string connectionString, bool isConnectName = true)
        {
            if (isConnectName)
            {
                ConnectionString = ConfigUtil.GetValue(connectionString);
            }
            else
            {
                ConnectionString = connectionString;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="connectionName"></param>
        public MsSqlDapperClient(string fileName, string connectionName)
        {
            ConnectionString = ConfigUtil.GetValue(fileName, connectionName);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="fileDir">文件目录</param>
        /// <param name="fileName">文件名称</param>
        /// <param name="connectionName">配置名称</param>
        public MsSqlDapperClient(string fileDir, string fileName, string connectionName)
        {
            ConnectionString = ConfigUtil.GetValue(fileDir, fileName, connectionName);
        }

        /// <summary>
        /// 获取连接对象
        /// </summary>
        /// <returns></returns>
        private SqlConnection GetConnection()
        {
            return new SqlConnection(ConnectionString);
        }

        /// <summary>
        /// 获取单条记录信息
        /// </summary>
        /// <typeparam name="T">返回结果</typeparam>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数对象</param>
        /// <param name="commandTimeout">超时时间</param>
        /// <returns></returns>
        public T GetFirstOrDefault<T>(string sql, object param = null, int? commandTimeout = null)
        {
            using var db = GetConnection();
            return db.QueryFirstOrDefault<T>(sql, param, null, commandTimeout);
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <typeparam name="T">返回结果</typeparam>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数对象</param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public IEnumerable<T> GetList<T>(string sql, object param = null, int? commandTimeout = null)
        {
            using var db = GetConnection();
            return db.Query<T>(sql, param, null, true, commandTimeout);
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <typeparam name="T">返回结果</typeparam>
        /// <param name="tableName">表名称</param>
        /// <param name="fields">显示字段</param>
        /// <param name="where">过滤条件</param>
        /// <param name="orderby">排序语句</param>
        /// <param name="param">参数对象</param>
        /// <param name="pageIndex">分页的页码</param>
        /// <param name="pageSize">分页大小</param>
        /// <param name="commandTimeout">超时时间</param>
        /// <returns></returns>
        public PageResult<T> GetPageList<T>(string tableName, string fields, string where, string orderby, object param = null, int pageIndex = 1, int pageSize = 20, int? commandTimeout = null)
        {
            var result = new PageResult<T>();
            if (pageIndex <= 0)
                pageIndex = 1;

            var paramters = new DynamicParameters(param);
            paramters.Add("@PageSize", pageSize, DbType.Int32);
            paramters.Add("@RowNumber", (pageIndex - 1) * pageSize, DbType.Int32);
            StringBuilder builderSql = ConvertPageSql(tableName, fields, where, orderby);

            using var db = GetConnection();
            using var multiple = db.QueryMultiple(builderSql.ToString(), paramters, commandTimeout: commandTimeout);
            var total = multiple.ReadFirst<int>();
            if (total > 0)
            {
                result = new PageResult<T>()
                {
                    Total = total,
                    PageList = multiple.Read<T>()
                };
            }
            return result;
        }

        /// <summary>
        /// 数据库操作执行方法（不带事务处理）
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public bool Execute(string sql, object param = null, int? commandTimeout = null)
        {
            using var db = GetConnection();
            return db.Execute(sql, param, null, commandTimeout) > 0;
        }

        /// <summary>
        /// 分页数据库操作执行方法（不带事务处理）
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paramters">请求参数</param>
        /// <param name="pageSize">分页大小</param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public bool Execute<T>(string sql, IEnumerable<T> paramters, int pageSize = 2000, int? commandTimeout = null) where T : class
        {
            if (paramters == null || !paramters.Any())
                return false;

            using var db = GetConnection();
            var totalPage = (int)Math.Ceiling((decimal)paramters.Count() / pageSize);//总页数
            for (int pageIndex = 1; pageIndex <= totalPage; pageIndex++)
            {
                var pageParamterList = paramters.Skip((pageIndex - 1) * pageSize).Take(pageSize);//分页列表
                db.Execute(sql, pageParamterList, null, commandTimeout); //分段执行
            }
            return true;
        }

        /// <summary>
        /// 数据库操作执行方法（带事务处理）
        /// </summary>
        /// <param name="action">该action用来处理需要批量执行的sql语句</param>
        /// <returns></returns>
        public bool Execute(Action<IDbConnection> action)
        {
            var isSuccess = false;
            using (var db = GetConnection())
            {
                //事务开始
                var transaction = db.BeginTransaction();
                try
                {
                    //执行数据库操作方法
                    action(db);

                    //提交事务
                    transaction.Commit();
                    isSuccess = true;
                }
                catch (Exception)
                {
                    //事务回滚
                    transaction.Rollback();
                }
            }
            return isSuccess;
        }

        /// <summary>
        /// 数据库操作执行方法（不带事务处理）
        /// </summary>
        /// <typeparam name="T">指定返回的对象</typeparam>
        /// <param name="sql">sql脚本</param>
        /// <param name="param">sql脚本对应的参数</param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public T ExecuteScalar<T>(string sql, object param = null, int? commandTimeout = null)
        {
            using var db = GetConnection();
            return db.ExecuteScalar<T>(sql, param, null, commandTimeout);
        }

        /// <summary>
        /// 获取单条记录信息
        /// </summary>
        /// <typeparam name="T">返回结果</typeparam>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数对象</param>
        /// <param name="commandTimeout">超时时间</param>
        /// <returns></returns>
        public async Task<T> GetFirstOrDefaultAsync<T>(string sql, object param = null, int? commandTimeout = null)
        {
            using var db = GetConnection();
            return await db.QueryFirstOrDefaultAsync<T>(sql, param, null, commandTimeout);
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <typeparam name="T">返回结果</typeparam>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数对象</param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public async Task<IEnumerable<T>> GetListAsync<T>(string sql, object param = null, int? commandTimeout = null)
        {
            using var db = GetConnection();
            return await db.QueryAsync<T>(sql, param, null, commandTimeout);
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <typeparam name="T">返回结果</typeparam>
        /// <param name="tableName">表名称</param>
        /// <param name="fields">显示字段</param>
        /// <param name="where">过滤条件</param>
        /// <param name="orderby">排序语句</param>
        /// <param name="param">参数对象</param>
        /// <param name="pageIndex">分页的页码</param>
        /// <param name="pageSize">分页大小</param>
        /// <param name="commandTimeout">超时时间</param>
        /// <returns></returns>
        public async Task<PageResult<T>> GetPageListAsync<T>(string tableName, string fields, string where, string orderby, object param = null, int pageIndex = 1, int pageSize = 20, int? commandTimeout = null)
        {
            var result = new PageResult<T>();
            if (pageIndex <= 0)
                pageIndex = 1;

            var paramters = new DynamicParameters(param);
            paramters.Add("@PageSize", pageSize, DbType.Int32);
            paramters.Add("@RowNumber", (pageIndex - 1) * pageSize, DbType.Int32);
            StringBuilder builderSql = ConvertPageSql(tableName, fields, where, orderby);

            using var db = GetConnection();
            using var multiple = await db.QueryMultipleAsync(builderSql.ToString(), paramters, commandTimeout: commandTimeout);
            var total = await multiple.ReadFirstAsync<int>();
            if (total > 0)
            {
                result = new PageResult<T>()
                {
                    Total = total,
                    PageList = await multiple.ReadAsync<T>()
                };
            }
            return result;
        }

        /// <summary>
        /// 数据库操作执行方法（不带事务处理）
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="commandTimeout">超时时间</param>
        /// <returns></returns>
        public async Task<bool> ExecuteAsync(string sql, object param = null, int? commandTimeout = null)
        {
            using var db = GetConnection();
            return await db.ExecuteAsync(sql, param, null, commandTimeout) > 0;
        }

        /// <summary>
        /// 分页数据库操作执行方法（不带事务处理）
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paramters">请求参数</param>
        /// <param name="pageSize">分页大小</param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public async Task<bool> ExecuteAsync<T>(string sql, IEnumerable<T> paramters, int pageSize = 2000, int? commandTimeout = null) where T : class
        {
            if (paramters == null || !paramters.Any())
                return false;

            using var db = GetConnection();
            var totalPage = (int)Math.Ceiling((decimal)paramters.Count() / pageSize);//总页数
            for (int pageIndex = 1; pageIndex <= totalPage; pageIndex++)
            {
                var pageParamterList = paramters.Skip((pageIndex - 1) * pageSize).Take(pageSize);//分页列表
                await db.ExecuteAsync(sql, pageParamterList, null, commandTimeout);//分段执行
            }
            return true;
        }

        /// <summary>
        /// 数据库操作执行方法（带事务处理）
        /// </summary>
        /// <param name="action">该action用来处理需要批量执行的sql语句</param>
        /// <returns></returns>
        public async Task<bool> ExecuteAsync(Action<IDbConnection> action)
        {
            var isSuccess = false;
            using (var db = GetConnection())
            {
                //事务开始
                var transaction = await db.BeginTransactionAsync();
                try
                {
                    //执行数据库操作方法
                    action(db);

                    //提交事务
                    await transaction.CommitAsync();
                    isSuccess = true;
                }
                catch (Exception)
                {
                    //事务回滚
                    await transaction.RollbackAsync();
                }
            }
            return isSuccess;
        }

        /// <summary>
        /// 数据库操作执行方法（不带事务处理）
        /// </summary>
        /// <typeparam name="T">指定返回的对象</typeparam>
        /// <param name="sql">sql脚本</param>
        /// <param name="param">sql脚本对应的参数</param>
        /// <param name="commandTimeout">超时时间</param>
        /// <returns></returns>
        public async Task<T> ExecuteScalarAsync<T>(string sql, object param = null, int? commandTimeout = null)
        {
            using var db = GetConnection();
            return await db.ExecuteScalarAsync<T>(sql, param, null, commandTimeout);
        }

        /// <summary>
        /// 批量新增
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dataLit"></param>
        /// <param name="tableName">表名(默认不填，使用TableName特性标记)</param>
        public void BatchAdd<T>(IEnumerable<T> dataLit, string tableName = "")
        {
            dataLit.BatchAdd(ConnectionString, DbTypeEnum.MsSql, tableName);
        }

        /// <summary>
        /// 转换分页Sql脚本
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="fields"></param>
        /// <param name="where"></param>
        /// <param name="orderby"></param>
        /// <returns></returns>
        private static StringBuilder ConvertPageSql(string tableName, string fields, string where, string orderby)
        {
            var regular = @"\s+";//多个空格的正则表达式
            tableName = Regex.Replace(tableName, regular, " ");
            where = Regex.Replace(where, regular, " ").Replace("where", "").TrimEnd(',', ';');
            orderby = Regex.Replace(orderby, regular, " ").Replace("order by", "").TrimEnd(',', ';');
            fields = Regex.Replace(fields, regular, " ").TrimStart(',', ';').TrimEnd(',', ';');

            StringBuilder builderSql = new StringBuilder();
            builderSql.Append($"select count(1) from {tableName} where 1=1 {where};");
            builderSql.Append($@"select {fields} from {tableName} where 1=1 {where} order by {orderby} offset @RowNumber rows fetch next @PageSize rows only;");
            return builderSql;
        }
    }
}